#import "DatabaseInteraction.h"
#import "Constants.h"
#import "URLLoadingOperation.h"

@implementation DatabaseInteraction

static NSString* database_name=@"database.sql";

static sqlite3_stmt *insert_statement = nil;
static sqlite3_stmt *directory_statement = nil;
static sqlite3_stmt *operation_statement = nil;


- (DatabaseInteraction *)init{
	[super init];
	[self checkAndCreateDatabase];
	return self;
}

- (void)dealloc
{
	[databasePath release];
	[super dealloc];
}

- (void)checkAndCreateDatabase
{
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    databasePath =[[documentsDirectory stringByAppendingPathComponent:database_name] retain];
	
	BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    success = [fileManager fileExistsAtPath:databasePath];
    if (success) 
		return;
	
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:database_name];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:databasePath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

- (void)resetDirectoryDatabase
{
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *dropTableSql="Drop Table directory";
	static char *createTableSql="CREATE TABLE directory ('UNID' CHAR(48) PRIMARY KEY, 'firstName' CHAR(32), 'lastName' CHAR(32), 'email' CHAR(48),'phoneNumber' CHAR(32), 'updated' char(32))";
	if (sqlite3_prepare_v2(database, dropTableSql, -1, &insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	int success = sqlite3_step(insert_statement);
	sqlite3_finalize(insert_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} else {
	}
	
	if (sqlite3_prepare_v2(database, createTableSql, -1, &insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	success = sqlite3_step(insert_statement);
	sqlite3_finalize(insert_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} else {
	}
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }	
	
}
- (void)saveDirectoryContactsToDatabase:(NSArray*)firstNameList lastName:(NSArray*)lastNameList UNID:(NSArray*)UNIDList{
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *sql = "INSERT INTO directory(UNID, firstName, lastName, updated) VALUES (?, ?, ?, ?)";
	int count=[firstNameList count];
	for(int i=0;i<count;i++){
		if (sqlite3_prepare_v2(database, sql, -1, &directory_statement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
		sqlite3_bind_text(directory_statement, 1, [[UNIDList objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(directory_statement, 2, [[firstNameList objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(directory_statement, 3, [[lastNameList objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(directory_statement, 4, [@"NO" UTF8String], -1, SQLITE_TRANSIENT);
		int success = sqlite3_step(directory_statement);
		// Because we want to reuse the statement, we "reset" it instead of "finalizing" it.
		sqlite3_finalize(directory_statement);
		if (success == SQLITE_ERROR) {
			NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
		} else {
		}	
	}
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }		
}


- (void)savePersonalContactsToDatabase:(NSArray*)list{
    if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *dropTableSql="Drop Table personal";
	static char *createTableSql="CREATE TABLE personal ('UNID' char(48) PRIMARY KEY, 'prefix' CHAR(16), 'firstName' CHAR(16),  'middleName' char(16),'lastName' char(16), 'suffix' char(16),'jobTitle' char(32), 'department' char(32), 'organization' char(48), 'email' char(48), 'street' char(32), 'city' char(32), 'state' char(32), 'zip' char(16), 'country' char(32),'mobile' char(32), 'workPhone' char(32), 'workFax' char(32), 'workPager' char(32), 'homeFax' char(32), 'updated' char(32))";
	if (sqlite3_prepare_v2(database, dropTableSql, -1, &insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	int success = sqlite3_step(insert_statement);
	sqlite3_finalize(insert_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} else {
	}
	
	
	if (sqlite3_prepare_v2(database, createTableSql, -1, &insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	success = sqlite3_step(insert_statement);
	sqlite3_finalize(insert_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} else {
	}
	

	
	static char *sql = "INSERT INTO personal(UNID, prefix, firstName, middleName, lastName, suffix, jobTitle, department, organization, email, street, city, state, zip, country, mobile, workPhone, workFax, workPager, homeFax, updated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	for(Person* person in list){	
		if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
		sqlite3_bind_text(insert_statement, 1, [[person UNID] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 2, [[person prefix] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 3, [[person firstName] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 4, [[person middleName] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 5, [[person lastName] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 6, [[person suffix] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 7, [[person jobTitle] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 8, [[person department] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 9, [[person company] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 10, [[person email] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 11, [[person street] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 12, [[person city] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 13, [[person state] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 14, [[person zip] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 15, [[person country] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 16, [[person mobile] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 17, [[person workPhone] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 18, [[person workFax] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 19, [[person workPager] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 20, [[person homeFax] UTF8String], -1, SQLITE_TRANSIENT);
		sqlite3_bind_text(insert_statement, 21, [@"NO" UTF8String], -1, SQLITE_TRANSIENT);
		int success = sqlite3_step(insert_statement);
		sqlite3_finalize(insert_statement);
		if (success == SQLITE_ERROR) {
			NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
		} else {
		}	
	}
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }	
}

- (void)updatePersonalWithPerson:(Person *)person{
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *sql = "UPDATE personal SET prefix=?,  middleName=?,  suffix=?,  email=?, street=?, city=?, state=?, zip=?, country=?, mobile=?, homeFax=?, workFax=?, workPager=?, updated=? WHERE UNID= ?";
	if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}

	sqlite3_bind_text(insert_statement, 1, [[person prefix] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 2, [[person middleName] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 3, [[person suffix] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 4, [[person email] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 5, [[person street] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 6, [[person city] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 7, [[person state] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 8, [[person zip] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 9, [[person country] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 10, [[person mobile] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 11, [[person homeFax] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 12, [[person workFax] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 13, [[person workPager] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 14,  [@"YES" UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(insert_statement, 15, [[person UNID] UTF8String], -1, SQLITE_TRANSIENT);

	int success = sqlite3_step(insert_statement);
	sqlite3_finalize(insert_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} 
	if (sqlite3_close(database) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
	}	
	
}

- (void)updateDirectoryUseUNID:(NSString*)UNID withEmail:(NSString *)email phone:(NSString*)phoneNumber{
	
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *sql = "UPDATE directory SET email=?, phoneNumber=?, updated=? WHERE UNID=?";
	if (sqlite3_prepare_v2(database, sql, -1, &directory_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_bind_text(directory_statement, 1, [email UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(directory_statement, 2, [phoneNumber UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(directory_statement, 3, [@"YES" UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(directory_statement, 4, [UNID UTF8String], -1, SQLITE_TRANSIENT);
	int success = sqlite3_step(directory_statement);
	sqlite3_finalize(directory_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to update the database with message '%s'.", sqlite3_errmsg(database));
	} 
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }		
}

- (void)saveOperationToDatabase:(NSURL *)URL type:(int)type data:(NSData*)data description:(NSString*)description{
	
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *sql = "INSERT INTO operation(URL, type, data, description) VALUES (?, ?, ?, ?)";
	if (sqlite3_prepare_v2(database, sql, -1, &operation_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_bind_text(operation_statement, 1, [[URL absoluteString] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(operation_statement, 2, [[NSString stringWithFormat:@"%d",type] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(operation_statement, 3, [[[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] autorelease] UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(operation_statement, 4, [description UTF8String], -1, SQLITE_TRANSIENT);
	int success = sqlite3_step(operation_statement);
	sqlite3_finalize(operation_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} 
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }		
}

- (NSArray*)getOperationsFromDatabase {
	NSMutableArray* operationArray=[[NSMutableArray alloc] init];
	URLLoadingOperation* operation;
	NSURL* URL;
	int type;
	NSData* data;
	NSString* description;
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	const char *sql = "SELECT URL, type, data, description FROM operation";
	if (sqlite3_prepare_v2(database, sql, -1, &operation_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	while (sqlite3_step(operation_statement) == SQLITE_ROW) {
		URL=[NSURL URLWithString:[NSString stringWithUTF8String:(char *)sqlite3_column_text(operation_statement, 0)]];
		type=[[NSString stringWithUTF8String:(char *)sqlite3_column_text(operation_statement, 1)] intValue];
		data=[[NSString stringWithUTF8String:(char *)sqlite3_column_text(operation_statement, 2)] dataUsingEncoding:NSUTF8StringEncoding];
		description=[NSString stringWithUTF8String:(char *)sqlite3_column_text(operation_statement, 3)] ;
		if(type==2 || type==3 || type==6){
			operation=[[URLLoadingOperation alloc] initWithType:type URL:URL data:data description:description fromDatabase:YES];
			[operationArray addObject:operation];
			[operation release];
		}

	} 
	sqlite3_finalize(operation_statement);
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }	
	return [operationArray autorelease];
}

- (void)deleteOperationInDatabase:(NSURL *)URL{
	
	if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
		sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
	}
	static char *sql = "DELETE FROM operation WHERE URL=?";
	if (sqlite3_prepare_v2(database, sql, -1, &operation_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_bind_text(operation_statement, 1, [[URL absoluteString] UTF8String], -1, SQLITE_TRANSIENT);
	int success = sqlite3_step(operation_statement);
	sqlite3_finalize(operation_statement);
	if (success == SQLITE_ERROR) {
		NSAssert1(0, @"Error: failed to insert into the database with message '%s'.", sqlite3_errmsg(database));
	} 
	if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
    }		
}

@end

